import pandas as pd

# Read the two Excel files
qwtb_df = pd.read_excel('xbzk-0314cd.xlsx')
paired_df = pd.read_excel('paired_results_xb.xlsx')
paired_df = paired_df[paired_df['POS_NEG'].astype(str).str.isnumeric()]

# Create a list to store matching rows with POS_NEG values
matching_rows = []

# Iterate through QWTB_POS+NEG dataframe
for _, qwtb_row in qwtb_df.iterrows():
    # Find matching rows in paired_results based on Formula
    matching_paired_rows = paired_df[paired_df['Formula'] == qwtb_row['Formula']]
    
    # Check if any matching rows have RT difference less than 0.05
    for _, paired_row in matching_paired_rows.iterrows():
        if abs(qwtb_row['RT [min]'] - paired_row['RT [min]']) < 0.05:
            # Create a copy of the qwtb_row
            matched_row = qwtb_row.copy()
            # Add the POS_NEG value from the paired row
            matched_row['POS_NEG'] = paired_row['POS_NEG']
            matching_rows.append(matched_row)
            break  # Take only the first match if there are multiple

# Create a new dataframe with matching rows
result_df = pd.DataFrame(matching_rows)

# Save to a new Excel file
result_df.to_excel('filtered_results_xb.xlsx', index=False)

print(f"Filtered results saved. Total matching rows: {len(result_df)}")